package com.moody.hibernate.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.LockMode;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.moody.hibernate.domain.CustInfo;
import com.moody.hibernate.domain.PdH;
import com.moody.hibernate.domain.RiskReportIRDetail;
import com.moody.init.DataDictionary;
import com.moody.util.SortUtil;

/**
 * A data access object (DAO) providing persistence and search support for PdH
 * entities. Transaction control of the save(), update() and delete() operations
 * can directly support Spring container-managed transactions or they can be
 * augmented to handle user-managed Spring transactions. Each of these methods
 * provides additional information for how to configure it for the desired type
 * of transaction control.
 * 
 * @see com.com.moody.hibernate.domain.PdH
 * @author MyEclipse Persistence Tools
 */

public class PdHDAO extends HibernateDaoSupport {
	private static final Logger log = LoggerFactory.getLogger(PdHDAO.class);
	// property constants
	public static final String CUST_ID = "custId";
	public static final String MODEL_ID = "modelId";
	public static final String QUALITATIVE_VALUE = "qualitativeValue";
	public static final String RATIO_VALUE = "ratioValue";
	public static final String ADJUST_VALUE = "adjustValue";
	public static final String SYSTEM_PDLEVEL = "systemPdlevel";
	public static final String SYSTEM_PDVALUE = "systemPdvalue";
	public static final String SYSTEM_DATE = "systemDate";
	public static final String FIRST_USER_ID = "firstUserId";
	public static final String FIRST_ORG_ID = "firstOrgId";
	public static final String FIRST_PDLEVEL = "firstPdlevel";
	public static final String FIRST_PDVALUE = "firstPdvalue";
	public static final String FIRST_REASON = "firstReason";
	public static final String FIRST_DATE = "firstDate";
	public static final String SECOND_USER_ID = "secondUserId";
	public static final String SECOND_ORG_ID = "secondOrgId";
	public static final String SECOND_PDLEVEL = "secondPdlevel";
	public static final String SECOND_PDVALUE = "secondPdvalue";
	public static final String SECOND_REASON = "secondReason";
	public static final String SECOND_DATE = "secondDate";
	public static final String CONFIRM_USER_ID = "confirmUserId";
	public static final String CONFIRM_ORG_ID = "confirmOrgId";
	public static final String CONFIRM_PDLEVEL = "confirmPdlevel";
	public static final String CONFIRM_PDVALUE = "confirmPdvalue";
	public static final String CONFIRM_REASON = "confirmReason";
	public static final String CONFIRM_DATE = "confirmDate";
	public static final String PHASE_STAT = "phaseStat";

	protected void initDao() {
		// do nothing
	}

	public void save(PdH transientInstance) {
		log.debug("saving PdH instance");
		try {
			getHibernateTemplate().save(transientInstance);
			log.info("save successful");
		} catch (RuntimeException re) {
			log.error("save failed", re);
			throw re;
		}
	}

	public void delete(PdH persistentInstance) {
		log.debug("deleting PdH instance");
		try {
			getHibernateTemplate().delete(persistentInstance);
			log.debug("delete successful");
		} catch (RuntimeException re) {
			log.error("delete failed", re);
			throw re;
		}
	}

	public PdH findById(java.lang.String id) {
		log.debug("getting PdH instance with id: " + id);
		try {
			PdH instance = (PdH) getHibernateTemplate().get(
					"com.moody.hibernate.domain.PdH", id);
			return instance;
		} catch (RuntimeException re) {
			log.error("get failed", re);
			throw re;
		}
	}

	public List findByExample(PdH instance) {
		log.debug("finding PdH instance by example");
		try {
			List results = getHibernateTemplate().findByExample(instance);
			log.debug("find by example successful, result size: "
					+ results.size());
			return results;
		} catch (RuntimeException re) {
			log.error("find by example failed", re);
			throw re;
		}
	}

	public List findByProperty(String propertyName, Object value) {
		log.debug("finding PdH instance with property: " + propertyName
				+ ", value: " + value);
		try {
			String queryString = "from PdH as model where model."
					+ propertyName + "= ?";
			return getHibernateTemplate().find(queryString, value);
		} catch (RuntimeException re) {
			log.error("find by property name failed", re);
			throw re;
		}
	}

	public List findByCustId(Object custId) {
		return findByProperty(CUST_ID, custId);
	}

	public List findByModelId(Object modelId) {
		return findByProperty(MODEL_ID, modelId);
	}

	public List findByQualitativeValue(Object qualitativeValue) {
		return findByProperty(QUALITATIVE_VALUE, qualitativeValue);
	}

	public List findByRatioValue(Object ratioValue) {
		return findByProperty(RATIO_VALUE, ratioValue);
	}

	public List findByAdjustValue(Object adjustValue) {
		return findByProperty(ADJUST_VALUE, adjustValue);
	}

	public List findBySystemPdlevel(Object systemPdlevel) {
		return findByProperty(SYSTEM_PDLEVEL, systemPdlevel);
	}

	public List findBySystemPdvalue(Object systemPdvalue) {
		return findByProperty(SYSTEM_PDVALUE, systemPdvalue);
	}

	public List findBySystemDate(Object systemDate) {
		return findByProperty(SYSTEM_DATE, systemDate);
	}

	public List findByFirstUserId(Object firstUserId) {
		return findByProperty(FIRST_USER_ID, firstUserId);
	}

	public List findByFirstOrgId(Object firstOrgId) {
		return findByProperty(FIRST_ORG_ID, firstOrgId);
	}

	public List findByFirstPdlevel(Object firstPdlevel) {
		return findByProperty(FIRST_PDLEVEL, firstPdlevel);
	}

	public List findByFirstPdvalue(Object firstPdvalue) {
		return findByProperty(FIRST_PDVALUE, firstPdvalue);
	}

	public List findByFirstReason(Object firstReason) {
		return findByProperty(FIRST_REASON, firstReason);
	}

	public List findByFirstDate(Object firstDate) {
		return findByProperty(FIRST_DATE, firstDate);
	}

	public List findBySecondUserId(Object secondUserId) {
		return findByProperty(SECOND_USER_ID, secondUserId);
	}

	public List findBySecondOrgId(Object secondOrgId) {
		return findByProperty(SECOND_ORG_ID, secondOrgId);
	}

	public List findBySecondPdlevel(Object secondPdlevel) {
		return findByProperty(SECOND_PDLEVEL, secondPdlevel);
	}

	public List findBySecondPdvalue(Object secondPdvalue) {
		return findByProperty(SECOND_PDVALUE, secondPdvalue);
	}

	public List findBySecondReason(Object secondReason) {
		return findByProperty(SECOND_REASON, secondReason);
	}

	public List findBySecondDate(Object secondDate) {
		return findByProperty(SECOND_DATE, secondDate);
	}

	public List findByConfirmUserId(Object confirmUserId) {
		return findByProperty(CONFIRM_USER_ID, confirmUserId);
	}

	public List findByConfirmOrgId(Object confirmOrgId) {
		return findByProperty(CONFIRM_ORG_ID, confirmOrgId);
	}

	public List findByConfirmPdlevel(Object confirmPdlevel) {
		return findByProperty(CONFIRM_PDLEVEL, confirmPdlevel);
	}

	public List findByConfirmPdvalue(Object confirmPdvalue) {
		return findByProperty(CONFIRM_PDVALUE, confirmPdvalue);
	}

	public List findByConfirmReason(Object confirmReason) {
		return findByProperty(CONFIRM_REASON, confirmReason);
	}

	public List findByConfirmDate(Object confirmDate) {
		return findByProperty(CONFIRM_DATE, confirmDate);
	}

	public List findByPhaseStat(Object phaseStat) {
		return findByProperty(PHASE_STAT, phaseStat);
	}

	public List findAll() {
		log.debug("finding all PdH instances");
		try {
			String queryString = "from PdH";
			return getHibernateTemplate().find(queryString);
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}

	public PdH merge(PdH detachedInstance) {
		log.debug("merging PdH instance");
		try {
			PdH result = (PdH) getHibernateTemplate().merge(detachedInstance);
			log.debug("merge successful");
			return result;
		} catch (RuntimeException re) {
			log.error("merge failed", re);
			throw re;
		}
	}

	public void attachDirty(PdH instance) {
		log.debug("attaching dirty PdH instance");
		try {
			getHibernateTemplate().saveOrUpdate(instance);
			log.debug("attach successful");
		} catch (RuntimeException re) {
			log.error("attach failed", re);
			throw re;
		}
	}

	public void attachClean(PdH instance) {
		log.debug("attaching clean PdH instance");
		try {
			getHibernateTemplate().lock(instance, LockMode.NONE);
			log.debug("attach successful");
		} catch (RuntimeException re) {
			log.error("attach failed", re);
			throw re;
		}
	}

	public static PdHDAO getFromApplicationContext(ApplicationContext ctx) {
		return (PdHDAO) ctx.getBean("PdHDAO");
	}

	/*
	 * 以下代码非自动生成，若进行逆向生成时，请勿覆盖
	 */

	@SuppressWarnings("unchecked")
	public List<PdH> findNonCorfirmPdH() {
		String queryString = "from PdH as model where model.phaseStat <> ?";
		return getHibernateTemplate().find(queryString,PdH.CONFIRM_FINISH);
	}

	@SuppressWarnings("unchecked")
	public List<PdH> findSortedConfirmedList(String custId) {
		String queryString = "from PdH as model where model.phaseStat = ? and model.custId = ?  order by model.systemDate desc";
		List<PdH> list = getHibernateTemplate().find(queryString,PdH.CONFIRM_FINISH,custId);
		list = SortUtil.sortByDate(list, "systemDate", 0);
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<PdH> listCustSortedRating() {
		List<PdH> resultList = new ArrayList<PdH>(); 
		List<Object[]> list;
		String sql = "select * from (select * from pd_h where pd_h.PhaseStat != '08' order by pd_h.PhaseStat, pd_h.SystemDate desc ) as model group by model.CustID";
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		list =  session.createSQLQuery(sql).list();
		for(Object[] o:list){   
	         resultList.add(this.findById(o[0].toString()));   
	     }     
		session.close();
		resultList = SortUtil.sortByDate(resultList, "systemDate", 0);
		return resultList;
	}
	
	@SuppressWarnings("unchecked")
	public List<PdH> listCustSortedRatingByUser(String userId, List<String> orgIdList, boolean isRetail) {
		List<PdH> resultList = new ArrayList<PdH>(); 
		List<Object[]> list;
		String orgIdListString = "";
		if(orgIdList != null && orgIdList.size() != 0){
			for(String orgId :orgIdList){
				orgIdListString += "'" + orgId +"',";
			}
			orgIdListString = orgIdListString.substring(0, orgIdListString.length() - 1);
		}
		else orgIdListString = "''";
		
		String sql = "select * from (select * from pd_h where CustID in ( select CustID from cust_info where inputUserId = '"+userId+"' or inputOrgId in ("+orgIdListString+")) and pd_h.PhaseStat != '08' order by pd_h.PhaseStat, pd_h.SystemDate desc ) as model group by model.CustID";
		if(isRetail) sql = "select * from (select * from pd_h where CustID in ( select CustID from ind_info where inputUserId = '"+userId+"' or inputOrgId in ("+orgIdListString+")) and pd_h.PhaseStat != '08' order by pd_h.PhaseStat, pd_h.SystemDate desc ) as model group by model.CustID";
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		list =  session.createSQLQuery(sql).list();
		for(Object[] o:list){   
	         resultList.add(this.findById(o[0].toString()));   
	     }     
		session.close();
		resultList = SortUtil.sortByDate(resultList, "systemDate", 0);
		return resultList;
	}

	@SuppressWarnings("unchecked")
	public List<PdH> findAllPDByCustId(String custId) {
		String queryString = "from PdH as model where model.custId = ? order by model.firstDate";
		List<PdH> list = getHibernateTemplate().find(queryString,custId);
		list = SortUtil.sortByDate(list, "firstDate", 1);
		return list;
	}

	public void update(PdH ratingPD) {
		log.debug("saving CustInfo instance");
		try {
			getHibernateTemplate().update(ratingPD);
			log.debug("save successful");
		} catch (RuntimeException re) {
			log.error("save failed", re);
			throw re;
		}
	}

	public PdH findNewestPDByCustId(String custId) {
		String queryString = "from PdH as model where model.custId = ? order by model.confirmDate desc";
		@SuppressWarnings("unchecked")
		List<PdH> list = getHibernateTemplate().find(queryString,custId);
		list = SortUtil.sortByDate(list, "confirmDate", 0);
		if(list.size() > 0 )
			return list.get(0);
		else
			return null;
			
	}
	
	public List<RiskReportIRDetail> findIRDetailByCondition(String queryCustType2,String date,String orgId,String userId){
		List<RiskReportIRDetail> resultList = new ArrayList<RiskReportIRDetail>();
		
		String custTypeInString = "'01','02','03'";//查询不包含零售客户
		if("01".equals(queryCustType2)) custTypeInString = "'01','02'";//对公客户
		else if("02".equals(queryCustType2))custTypeInString = "'03'";//同业客户
		
		String hql = "from PdH as p, CustInfo as c where p.custId = c.custId and c.custType in (" + custTypeInString+ ") and p.confirmDate like :date and p.confirmUserId like :userId and p.confirmOrgId like :orgId";
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		Query query = session.createQuery(hql);
		query.setString("date", "%"+date+"%");
		query.setString("orgId", "%" +orgId+"%");
		query.setString("userId", "%"+userId+"%");
		
		List<Object> list = query.list();
		for(Object item:list){
			Object[] a = (Object[]) item;
			@SuppressWarnings("unused")
			PdH pdH = (PdH) a[0];
			@SuppressWarnings("unused")
			CustInfo custInfo = (CustInfo) a[1];
			RiskReportIRDetail ir = new RiskReportIRDetail();
			ir.setArea("");
			ir.setConfirmDate(pdH.getConfirmDate());
			ir.setConfirmOrgID(pdH.getConfirmOrgId());
			ir.setCreditTime(custInfo.getRelationshipDate());
			ir.setCustID(custInfo.getCustId());
			ir.setCustName(custInfo.getCustName());
			ir.setCustType(DataDictionary.getInstance().getNameByCodeAndId("CustType", custInfo.getCustType()));
			ir.setCustScope(DataDictionary.getInstance().getNameByCodeAndId("CustScope", custInfo.getCustScope()));
			ir.setExposureType(custInfo.getExposureType());
			ir.setFirstOrgID(pdH.getFirstOrgId());
			ir.setIndustryType(DataDictionary.getInstance().getNameByCodeAndId("IndustryType", custInfo.getIndustryType()));
			ir.setNewestPD(pdH.getConfirmPdlevel());
			ir.setNewestPDReportNo("");
			ir.setOrgType(DataDictionary.getInstance().getNameByCodeAndId("OrgType", custInfo.getOrgType()));
			
			resultList.add(ir);
			
		}
		session.close();
		return resultList;
	}

	public List<PdH> listCustSortedRatingByConfirmDate() {
		List<PdH> resultList = new ArrayList<PdH>(); 
		List<Object[]> list;
		String sql = "select * from (select * from pd_h  order by pd_h.ConfirmDate desc) as model group by model.CustID order by model.ConfirmPDLevel desc";
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		list =  session.createSQLQuery(sql).list();
		
		for(Object[] o:list){   
	         resultList.add(this.findById(o[0].toString()));   
	     }     
		session.close();
		return resultList;
	}
	
	public void invalidatePdH(String custId){
		String sql = "update pd_h set PhaseStat = '08' WHERE CustID = '"+ custId +"' and PhaseStat != '06' and PhaseStat != '05'";
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		Transaction trans = session.beginTransaction();
		trans.begin();
		SQLQuery sQLQuery=session.createSQLQuery(sql);
		sQLQuery.executeUpdate(); 
		trans.commit();
		session.close();
	}
}